In addition to extract or read/write support on single table with spatial tables, Bentley Map supports the concept of storing annotations in SQL Server. This requires a separate spatial table that is related to the table with the parent feature in it. Adding an annotation table is a three step process:

• First, a spatial table is created;

• Second, a composite primary key is added to the table;

• Finally, a foreign key is added to ‘formalize’ the relationship between the annotation table and the parent table.

The following spatial table is used as the parent table for annotation:

CREATE TABLE PARCELS 
(Owner VARCHAR(20),
Market_Value VARCHAR(10),
Parcel_ID INT PRIMARY KEY,
Geometry GEOMETRY)

The following the CREATE TABLE statement is used for a table that stores annotations for the parcels table:

CREATE TABLE OWNER_TEXT 
(Owner_Text_ID INT CONSTRAINT Owner_Text_ID PRIMARY KEY,
Parcel_ID_REF INT NOT NULL,
MS_Angle FLOAT,
MS_X_Scale FLOAT,
MS_Y_Scale FLOAT,
Geometry GEOMETRY)

A foreign key constraint needs to be created to express the relationship between the data in the parent table and the table used to store annotations.

Adding a foreign key to link the Owner_Text table to the customers table requires the following ALTER TABLE statement:

ALTER TABLE OWNER_TEXT
WITH NOCHECK
ADD CONSTRAINT OWNER_TEXT_FK 
FOREIGN KEY (Parcel_ID_REF) 
REFERENCES PARCELS (Parcel_ID)
ON DELETE CASCADE

This will ensure that the two tables are tied together and that a delete of a row in the root table will trigger a delete of all the rows that in the annotation table that have a reference to the deleted root table row.

The combination of the root and annotation tables, connected through the foreign key should be sufficient for Bentley Map to extract/modify/post features with annotations.